package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.DeliveryAddressDao;
import com.foreknow.elm.po.DeliveryAddress;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeliveryAddressDaoImpl implements DeliveryAddressDao {
    private Connection con = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;

    @Override
    public List<DeliveryAddress> listDeliveryAddressByUserId(String userId) throws Exception {
        String sql = "select * from deliveryAddress where userId=?";
        List<DeliveryAddress> list = new ArrayList<>();
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, userId);
            rs = pst.executeQuery();
            while (rs.next()) {
                DeliveryAddress address = new DeliveryAddress();
                address.setDaId(rs.getInt("daId"));
                address.setContactName(rs.getString("contactName"));
                address.setContactSex(rs.getInt("contactSex"));
                address.setContactTel(rs.getString("contactTel"));
                address.setAddress(rs.getString("address"));
                address.setUserId(rs.getString("userId"));
                list.add(address);
            }
        } finally {
            DBUtil.close(rs, pst);
        }
        return list;
    }

    @Override
    public DeliveryAddress getDeliveryAddressById(int daId) throws Exception {
        String sql = "select * from deliveryAddress where daId=?";
        DeliveryAddress address = new DeliveryAddress();
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, daId);
            rs = pst.executeQuery();
            while (rs.next()) {
                address.setDaId(rs.getInt("daId"));
                address.setContactName(rs.getString("contactName"));
                address.setContactSex(rs.getInt("contactSex"));
                address.setContactTel(rs.getString("contactTel"));
                address.setAddress(rs.getString("address"));
                address.setUserId(rs.getString("userId"));
            }
        } finally {
            DBUtil.close(rs, pst);
        }
        return address;
    }

    @Override
    public int saveDeliveryAddress(DeliveryAddress address) throws Exception {
        String sql = "insert into deliveryAddress(contactName,contactSex,contactTel,address,userId) " +
                "values (?,?,?,?,?)";
        int row = 0;
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1,address.getContactName());
            pst.setInt(2,address.getContactSex());
            pst.setString(3,address.getContactTel());
            pst.setString(4,address.getAddress());
            pst.setString(5,address.getUserId());
            row = pst.executeUpdate();
        }finally {
            DBUtil.close(rs,pst);
        }
        return row;
    }

    @Override
    public int updateDeliveryAddress(DeliveryAddress address) throws Exception {
        String sql = "update deliveryAddress set contactName=?,contactSex=?,contactTel=?," +
                "address=?,userId=? where daId=?";
        int row = 0;
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1,address.getContactName());
            pst.setInt(2,address.getContactSex());
            pst.setString(3,address.getContactTel());
            pst.setString(4,address.getAddress());
            pst.setString(5,address.getUserId());
            pst.setInt(6,address.getDaId());
            row = pst.executeUpdate();
        }finally {
            DBUtil.close(rs,pst);
        }
        return row;
    }

    @Override
    public int removeDeliveryAddress(int daId) throws Exception {
        String sql = "delete from deliveryAddress where daId=?";
        int row = 0;
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1,daId);
            row = pst.executeUpdate();
        }finally {
            DBUtil.close(rs,pst);
        }
        return row;
    }
}
